﻿using black_screen_mode_server.Models;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Security.Cryptography;
using System.Management;
using OpenHardwareMonitor.Hardware;
using DocumentFormat.OpenXml;
using System.IO;
using Org.BouncyCastle.Bcpg.OpenPgp;
using ClosedXML.Excel;
using System.Data.SqlClient;




namespace black_screen_mode_server
{

    public static class ProjectTools
    {

        private static string iniFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "config", "sysConfig.ini");
        private static IniFile iniFile = new IniFile(iniFilePath.Trim());

        public static string configDbConnString
        {
            get
            {
                try
                {
                    Logger.Singleton.Info("配置文件目录：" + iniFilePath);
                    Logger.Singleton.Info("连接字符串：" + iniFile.Read("configDbConnection", "connString"));
                    return iniFile.Read("configDbConnection", "connString");
                }
                catch (Exception)
                {
                    Logger.Singleton.Error("获取配置文件失败！");
                    return "";
                }

                //return configuration.GetSection("configDbConnection")["connString"];
            }
        }

        public static string PKSServerName1
        {
            get
            {
                return iniFile.Read("eventDbConnection", "server_name1");

                //return configuration.GetSection("PKSServerName")["serverName1"];
            }
        }

        public static string PKSServerName2
        {
            get
            {
                return iniFile.Read("eventDbConnection", "server_name2");

                //return configuration.GetSection("PKSServerName")["serverName1"];
            }
        }

        public static string configDBName
        {
            get
            {
                return iniFile.Read("configDbConnection", "connectDB");

                //return configuration.GetSection("configDbConnection")["connectDB"];
            }
        }

        public static string LogLevel
        {
            get
            {
                return iniFile.Read("LogLevel", "level");
            }
        }
        public static EventDbConnectionModel eventDbConnectionModel
        {
            get
            {
                return new EventDbConnectionModel
                {
                    Conn1 = iniFile.Read("eventDbConnection", "conn1"),
                    Conn2 = iniFile.Read("eventDbConnection", "conn2"),
                    Host1 = iniFile.Read("eventDbConnection", "host_name1"),
                    Host2 = iniFile.Read("eventDbConnection", "host_name2"),
                    Port1 = iniFile.Read("eventDbConnection", "port1"),
                    Port2 = iniFile.Read("eventDbConnection", "port2"),
                    ServerName1 = iniFile.Read("eventDbConnection", "server_name1"),
                    ServerName2 = iniFile.Read("eventDbConnection", "server_name2")


                    //Conn1 = configuration.GetSection("eventDbConnection")["conn1"],
                    //Conn2 = configuration.GetSection("eventDbConnection")["conn2"]
                };
            }

            set
            {
                iniFile.Write("eventDbConnection", "conn1", value.Conn1);
                iniFile.Write("eventDbConnection", "conn2", value.Conn2);
                iniFile.Write("eventDbConnection", "host_name1", value.Host1);
                iniFile.Write("eventDbConnection", "host_name2", value.Host2);
                iniFile.Write("eventDbConnection", "port1", value.Port1);
                iniFile.Write("eventDbConnection", "port2", value.Port2);
                iniFile.Write("eventDbConnection", "server_name1", value.ServerName1);
                iniFile.Write("eventDbConnection", "server_name2", value.ServerName2);

                //configuration.GetSection("eventDbConnection")["conn1"] = value.Conn1;
                //configuration.GetSection("eventDbConnection")["conn2"] = value.Conn2;
            }
        }


        public static string RegisterKey()
        {
            return iniFile.Read("RegisterInfo", "code");

            //return configuration.GetSection("RegisterInfo")["code"];
        }

        public static string ReadConfig(string section, string key)
        {
            return iniFile.Read(section, key);
        }

        public static void WriteConfig(string section, string key, string value)
        {
            iniFile.Write(section, key, value);
        }

        public static int pointsNum()
        {
            return int.Parse(iniFile.Read("RegisterInfo", "pointsNum"));

            //return int.Parse(configuration.GetSection("RegisterInfo")["pointsNum"]);
        }
        public static TcpServerModel getTcpServer()
        {
            TcpServerModel tcpServerModel = new TcpServerModel();
            int portNumber = 0;
            int.TryParse(iniFile.Read("tcpServer", "portNumber"), out portNumber);
            //int.TryParse(configuration.GetSection("tcpServer")["portNumber"], out portNumber);

            tcpServerModel.IpAddress = iniFile.Read("tcpServer", "ipAddress");
            //tcpServerModel.IpAddress = configuration.GetSection("tcpServer")["ipAddress"];
            tcpServerModel.PortNumber = portNumber;

            return tcpServerModel;
        }

        public static ParametersInfo getParamInfos()
        {
            ParametersInfo info = new ParametersInfo();

            info.interval = int.Parse(iniFile.Read("parametersInfo", "interval"));
            info.delay_time = int.Parse(iniFile.Read("parametersInfo", "delay_time"));
            info.frequency = int.Parse(iniFile.Read("parametersInfo", "frequency"));
            info.ignore = int.Parse(iniFile.Read("parametersInfo", "ignore"));
            info.file_location_local = iniFile.Read("parametersInfo", "file_location_local");
            info.file_location_net = iniFile.Read("parametersInfo", "file_location_net");
            info.last_export_time = iniFile.Read("parametersInfo", "last_export_time");
            info.serverName = iniFile.Read("parametersInfo", "serverName");

            //info.interval = int.Parse(configuration.GetSection("parametersInfo")["interval"]);
            //info.delay_time = int.Parse(configuration.GetSection("parametersInfo")["delay_time"]);
            //info.frequency = int.Parse(configuration.GetSection("parametersInfo")["frequency"]);
            //info.ignore = int.Parse(configuration.GetSection("parametersInfo")["ignore"]);
            //info.file_location_local = configuration.GetSection("parametersInfo")["file_location_local"];
            //info.file_location_net = configuration.GetSection("parametersInfo")["file_location_net"];
            //info.last_export_time = configuration.GetSection("parametersInfo")["last_export_time"];
            //info.serverName = configuration.GetSection("parametersInfo")["serverName"];
            return info;
        }
        /// <summary>
        /// 根据PHD 中的 时间信息字符串获取格林威治时间
        /// </summary>
        /// <param name="timesamp"></param>
        /// <returns></returns>
        public static bool ConvertActiveTime(string timesamp, out DateTime actime)
        {
            bool rtn = false;
            actime = default(DateTime);
            try
            {
                string ticks = timesamp.Substring(timesamp.Length - 7, 7);
                string second = timesamp.Substring(0, timesamp.Length - 7);

                // 格林威治时间 1601/1/1
                DateTimeOffset basetime = new DateTimeOffset(1601, 1, 1, 0, 0, 0, TimeSpan.Zero);
                DateTimeOffset timeOffset = basetime.AddSeconds(double.Parse(second));
                actime = timeOffset.DateTime + new TimeSpan(long.Parse(ticks));
                rtn = true;
            }
            catch (Exception ex) { }
            return rtn;
        }

        //将格林威治时间转换为PHD时间
        public static string ConvertToPhdTime(DateTime time)
        {
            string rtn = string.Empty;
            try
            {
                // 格林威治时间 1601/1/1
                DateTimeOffset basetime = new DateTimeOffset(1601, 1, 1, 0, 0, 0, TimeSpan.Zero);
                DateTimeOffset testConvert = new DateTimeOffset(time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second, TimeSpan.Zero);

                string ticks = (time - basetime).Ticks.ToString();
                rtn = (testConvert - basetime).TotalSeconds.ToString().Substring(0, 11) + ticks.Substring(ticks.Length - 7, 7);

            }
            catch (Exception ex) { }
            return rtn;
        }

        public static string ConvertDateTimeToString(DateTime dt)
        {
            string ret = String.Empty;
            try
            {
                ret = dt.ToString("yyyy-MM-dd HH:mm:ss");
            }
            catch (Exception ex)
            {
                ret = String.Empty;
            }
            return ret;
        }

        public static string ConvertTimeSpanToStr(TimeSpan ts)
        {
            string ret = String.Empty;
            try
            {
                //ret = ts.ToString("%d") + "天" + ts.ToString("%h") + "小时" + ts.ToString("%m") + "分钟";
                ret = ts.ToString("dd") + ":" + ts.ToString("hh") + ":" + ts.ToString("mm");
            }
            catch (Exception ex)
            {
                ret = String.Empty;
            }
            return ret;
        }

        public static List<SummaryBlackScreenModel> GenerateSummarySql(string connectionString, string beginTime, string endTime)
        {
            List<SummaryBlackScreenModel> summaryBlackScreenModels = new List<SummaryBlackScreenModel>();
            try
            {
                string summaryBeginTime = beginTime;
                string summaryEndTime = endTime;
                //1.判断上次导出报表时间点是否存在黑屏记录，若存在，起始时间为查询的黑屏记录的起始时间，否则为上次导出时间
                string blackScreenQuery = $"select top 1 start_time from timing_report where end_time > '{beginTime}' and start_time < '{beginTime}' order by end_time asc";
                SqlHelper sqlHelper = new SqlHelper(connectionString);
                var readerBeginExist = sqlHelper.GetByReader(blackScreenQuery);
                if (readerBeginExist.HasRows)
                {
                    readerBeginExist.Read();
                    //存在黑屏记录
                    summaryBeginTime = readerBeginExist[0].ToString();
                }
                else
                {
                    summaryBeginTime = beginTime;
                }
                sqlHelper.ClosedCon();
                //2.判断当前结束导出时间点是否存在黑屏记录，若存在，结束时间为查询的黑屏记录的结束时间，否则为当前时间
                string blackScreenQueryEnd = $"select top 1 end_time from timing_report where end_time > '{endTime}' and start_time < '{endTime}' order by end_time desc";
                var readerEndExist = sqlHelper.GetByReader(blackScreenQueryEnd);
                if (readerEndExist.HasRows)
                {
                    readerEndExist.Read();
                    //存在黑屏记录
                    summaryEndTime = readerEndExist["end_time"].ToString();
                }
                else
                {
                    summaryEndTime = endTime;
                }
                sqlHelper.ClosedCon();
                //获取所有组名
                string groupQuery = "select id,Name from [group]";
                DataTable groupRet = sqlHelper.GetByTable(groupQuery);
                List<int> groupIds = new List<int>();
                List<string> groupNames = new List<string>();
                foreach (DataRow row in groupRet.Rows)
                {
                    groupIds.Add(int.Parse(row["id"].ToString()));
                    groupNames.Add(row["Name"].ToString());
                }

                for (int i = 0; i < groupIds.Count; i++)
                {
                    SummaryBlackScreenModel item = new SummaryBlackScreenModel();

                    //查询所有黑屏记录
                    string summaryQuery = $"select a.start_time, a.end_time, a.duration, b.Name, a.end_time - a.start_time as dur from timing_report a left join [group] b on a.groupId = b.id where start_time >= '{summaryBeginTime}' and end_time <= '{summaryEndTime}'and groupId = {groupIds[i]} order by dur desc";
                    DataTable summaryRet = sqlHelper.GetByTable(summaryQuery);
                    if (summaryRet.Rows.Count > 0)
                    {
                        item.Name = groupNames[i];
                        //拼凑summaryBlackScreenModel
                        item.StartTime = Convert.ToDateTime(summaryRet.Rows[0]["start_time"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                        item.EndTime = Convert.ToDateTime(summaryRet.Rows[0]["end_time"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                        string[] duration = summaryRet.Rows[0]["duration"].ToString().Split(':');
                        if (duration.Length == 3)
                        {
                            item.Duration = duration[0] + "天" + duration[1] + "小时" + duration[2] + "分";
                        }
                        else
                        {
                            item.Duration = "未知的数据";
                        }


                        double sumBlackScreenTime = 0;

                        for (int j = 0; j < summaryRet.Rows.Count; j++)
                        {
                            //计算黑屏率
                            TimeSpan ts = Convert.ToDateTime(summaryRet.Rows[j]["end_time"].ToString()) - Convert.ToDateTime(summaryRet.Rows[j]["start_time"].ToString());
                            if (ts.TotalSeconds < Convert.ToDouble(iniFile.Read("parametersInfo", "ignore")) * 60) continue;
                            //if (ts.TotalSeconds < Convert.ToDouble(ConfigurationTools.configuration.GetSection("parametersInfo")["ignore"]) * 60) continue;
                            sumBlackScreenTime += ts.TotalSeconds;
                        }

                        double totalDuration = (Convert.ToDateTime(summaryEndTime) - Convert.ToDateTime(summaryBeginTime)).TotalSeconds;

                        //黑屏率保留6位小数
                        item.BlackScreenRate = Math.Round(sumBlackScreenTime / totalDuration, 2);

                        summaryBlackScreenModels.Add(item);
                    }

                }

            }
            catch (Exception)
            {

                summaryBlackScreenModels = null;
            }


            return summaryBlackScreenModels;
        }


        public static List<SummaryBlackScreenModel> GenerateSummarySql2(string connectionString, string beginTime, string endTime)
        {
            if (string.IsNullOrWhiteSpace(connectionString) ||
                string.IsNullOrWhiteSpace(beginTime) ||
                string.IsNullOrWhiteSpace(endTime))
            {
                return new List<SummaryBlackScreenModel>();
            }

            try
            {
                DateTime begin = ParseDate(beginTime);
                DateTime end = ParseDate(endTime).AddDays(1);

                // 使用参数化查询，避免 SQL 注入
                string summaryQuery = @"
            SELECT 
                a.start_time,
                ISNULL(DATEDIFF(SECOND, LEAD(a.start_time) OVER (ORDER BY a.start_time DESC), a.start_time), 0) AS black_screen_duration
            FROM timing_report a
            WHERE a.start_time >= @BeginTime 
              AND a.start_time < @EndTime
            ORDER BY a.start_time DESC";

                using (var connection = new SqlConnection(connectionString))
                using (var command = new SqlCommand(summaryQuery, connection))
                {
                    command.Parameters.AddWithValue("@BeginTime", begin);
                    command.Parameters.AddWithValue("@EndTime", end);

                    connection.Open();
                    var adapter = new SqlDataAdapter(command);
                    var summary = new DataTable();
                    adapter.Fill(summary);

                    var result = new List<SummaryBlackScreenModel>(summary.Rows.Count);

                    foreach (DataRow row in summary.Rows)
                    {
                        var model = new SummaryBlackScreenModel();

                        // 处理 start_time
                        model.StartTime = row["start_time"] is DateTime startTime
                            ? startTime.ToString("yyyy-MM-dd")
                            : string.Empty;

                        // 处理 black_screen_duration（int 类型）
                        int seconds = row["black_screen_duration"] as int? ?? 0;
                        var ts = TimeSpan.FromSeconds(seconds);
                        model.Duration = $"{ts.Days}天 {ts.Hours}小时 {ts.Minutes}分钟";

                        result.Add(model);
                    }

                    return result;
                }
            }
            catch (Exception ex)
            {
                // ⚠️ 不要静默吞异常！至少记录日志
                // Log.Error("GenerateSummarySql2 failed", ex);
                throw; // 或返回空列表，但建议上层处理异常
            }
        }

        // 辅助方法：安全解析日期字符串
        private static DateTime ParseDate(string dateString)
        {
            if (DateTime.TryParse(dateString, out DateTime result))
            {
                return result.Date; // 确保时间为 00:00:00
            }
            throw new ArgumentException($"无法解析日期: '{dateString}'", nameof(dateString));
        }


        //导出Excel
        public static void ExportDataToExcel(string location, string fileName, string beginTime, string endTime)
        {
            //string dtLastExport = "2024-01-29 00:00:00";



            //按找分组工序创建不同的报表sheet
            SqlHelper sqlHelper = new SqlHelper(ProjectTools.configDbConnString);
            List<Models.GroupInfo> groupInfos = new List<Models.GroupInfo>();
            string groupInfoSql = $"select * from [group]";
            try
            {
                DataTable groupInfoDt = sqlHelper.GetByTable(groupInfoSql);
                if (groupInfoDt != null && groupInfoDt.Rows.Count > 0)
                {
                    for (int i = 0; i < groupInfoDt.Rows.Count; i++)
                    {
                        Models.GroupInfo gp = new Models.GroupInfo();
                        gp.Id = Convert.ToInt32(groupInfoDt.Rows[i]["id"]);
                        gp.Name = groupInfoDt.Rows[i]["Name"].ToString();
                        groupInfos.Add(gp);
                    }
                }
            }
            catch (Exception)
            {
                Logger.Singleton.Info("获取分组信息失败！");
            }

            //分别创建ReportData和SummaryData两个报表Excel
            using (XLWorkbook workbook = new XLWorkbook())
            {
                foreach (var info in groupInfos)
                {
                    var worksheet = workbook.Worksheets.Add(info.Name);
                    GenerateGroupSheet(worksheet, info.Name, beginTime, endTime);
                    //worksheet.Columns().AdjustToContents();
                    // worksheet.ColumnsUsed().AdjustToContents();
                }

                //汇总报告sheet生成
                var summarySheet = workbook.Worksheets.Add("SummaryData");

                var summaryData = ProjectTools.GenerateSummarySql(ProjectTools.configDbConnString, beginTime, endTime);
                if (summaryData != null)
                {
                    summarySheet.Cell(2, 2).Value = "名称";
                    summarySheet.Cell(2, 3).Value = "黑屏率(%)";
                    summarySheet.Cell(2, 4).Value = "最佳时长";
                    summarySheet.Cell(2, 5).Value = "开始时间";
                    summarySheet.Cell(2, 6).Value = "结束时间";

                    summarySheet.Column(2).Width = 10;
                    summarySheet.Column(3).Width = 20;
                    summarySheet.Column(4).Width = 20;
                    summarySheet.Column(5).Width = 20;
                    summarySheet.Column(6).Width = 20;



                    for (int i = 0; i < summaryData.Count; i++)
                    {
                        summarySheet.Cell(i + 3, 2).Value = summaryData[i].Name;
                        summarySheet.Cell(i + 3, 3).Value = summaryData[i].BlackScreenRate.ToString("F2");
                        summarySheet.Cell(i + 3, 4).Value = summaryData[i].Duration;
                        summarySheet.Cell(i + 3, 5).Value = summaryData[i].StartTime;
                        summarySheet.Cell(i + 3, 6).Value = summaryData[i].EndTime;
                    }


                }

                workbook.SaveAs(Path.Combine(location, fileName));
                workbook.Dispose();
            }

            ProjectTools.WriteConfig("parametersInfo", "last_export_time", endTime);
        }


        public static void ExportDataToExcel(string fullFileName, string beginTime, string endTime)
        {
            //string dtLastExport = "2024-01-29 00:00:00";



            //按找分组工序创建不同的报表sheet
            SqlHelper sqlHelper = new SqlHelper(ProjectTools.configDbConnString);
            List<Models.GroupInfo> groupInfos = new List<Models.GroupInfo>();
            string groupInfoSql = $"select * from [group]";
            try
            {
                DataTable groupInfoDt = sqlHelper.GetByTable(groupInfoSql);
                if (groupInfoDt != null && groupInfoDt.Rows.Count > 0)
                {
                    for (int i = 0; i < groupInfoDt.Rows.Count; i++)
                    {
                        Models.GroupInfo gp = new Models.GroupInfo();
                        gp.Id = Convert.ToInt32(groupInfoDt.Rows[i]["id"]);
                        gp.Name = groupInfoDt.Rows[i]["Name"].ToString();
                        groupInfos.Add(gp);
                    }
                }
            }
            catch (Exception)
            {
                Logger.Singleton.Info("获取分组信息失败！");
            }

            //分别创建ReportData、SummaryData、SummaryBlackData三个个报表Excel
            using (XLWorkbook workbook = new XLWorkbook())
            {
                foreach (var info in groupInfos)
                {
                    var worksheet = workbook.Worksheets.Add(info.Name);
                    GenerateGroupSheet(worksheet, info.Name, beginTime, endTime);
                    //worksheet.Columns().AdjustToContents();
                    // worksheet.ColumnsUsed().AdjustToContents();
                }

                //汇总报告sheet生成
                var summarySheet = workbook.Worksheets.Add("SummaryData");

                var summaryData = ProjectTools.GenerateSummarySql(ProjectTools.configDbConnString, beginTime, endTime);
                if (summaryData != null)
                {
                    summarySheet.Cell(2, 2).Value = "名称";
                    summarySheet.Cell(2, 3).Value = "黑屏率(%)";
                    summarySheet.Cell(2, 4).Value = "最佳时长";
                    summarySheet.Cell(2, 5).Value = "开始时间";
                    summarySheet.Cell(2, 6).Value = "结束时间";

                    summarySheet.Column(2).Width = 10;
                    summarySheet.Column(3).Width = 20;
                    summarySheet.Column(4).Width = 20;
                    summarySheet.Column(5).Width = 20;
                    summarySheet.Column(6).Width = 20;



                    for (int i = 0; i < summaryData.Count; i++)
                    {
                        summarySheet.Cell(i + 3, 2).Value = summaryData[i].Name;
                        summarySheet.Cell(i + 3, 3).Value = summaryData[i].BlackScreenRate.ToString("F2");
                        summarySheet.Cell(i + 3, 4).Value = summaryData[i].Duration;
                        summarySheet.Cell(i + 3, 5).Value = summaryData[i].StartTime;
                        summarySheet.Cell(i + 3, 6).Value = summaryData[i].EndTime;
                    }


                }

                #region BlackSummartData
                //汇总报告sheet生成
                var summaryBlackSheet = workbook.Worksheets.Add("SummaryBlackData");

                var summaryBlackData = ProjectTools.GenerateSummarySql2(ProjectTools.configDbConnString, beginTime, endTime);
                if (summaryBlackData != null)
                {
                    summaryBlackSheet.Cell(2, 2).Value = "序号";
                    summaryBlackSheet.Cell(2, 3).Value = "开始时间";
                    summaryBlackSheet.Cell(2, 4).Value = "运行时长";

                    summaryBlackSheet.Column(2).Width = 10;
                    summaryBlackSheet.Column(3).Width = 20;
                    summaryBlackSheet.Column(4).Width = 50;


                    for (int i = 0; i < summaryBlackData.Count; i++)
                    {
                        summaryBlackSheet.Cell(i + 3, 2).Value = i + 1;
                        summaryBlackSheet.Cell(i + 3, 3).Value = summaryBlackData[i].StartTime;
                        summaryBlackSheet.Cell(i + 3, 4).Value = summaryBlackData[i].Duration;
                    }

                }
                #endregion

                workbook.SaveAs(fullFileName);
                workbook.Dispose();
            }

            ProjectTools.WriteConfig("parametersInfo", "last_export_time", endTime);
        }
        private static void GenerateGroupSheet(IXLWorksheet sheet, string groupName, string beginTime, string endTime)
        {
            string query = $"select  start_time, end_time, duration, source, station,groupName, category_name,location_tag_name, priority,condition_name, description from timing_report where end_time > '{beginTime}' and end_time < '{endTime}'  and groupName ='{groupName}'";
            SqlHelper sqlHelper = new SqlHelper(ProjectTools.configDbConnString);
            var reader = sqlHelper.GetByReader(query);

            Dictionary<string, string> columnMappings = new Dictionary<string, string>
                            {
                                { "start_time", "起始时间" }, { "end_time", "结束时间" }, { "duration", "持续时间" },
                                { "source", "点位" }, { "category", "类型编号" }, { "category_name", "事件类型" }, { "area_code", "区域" },
                                { "priority", "优先级" }, { "condition_name", "事件描述" },{"block", "模块" },{"location_tag_name", "工段" },{ "description", "位号描述" },{"station","操作站点" },{ "groupName", "工序"}
                            };

            for (int i = 0; i < reader.FieldCount + 1; i++)
            {
                sheet.Column(i + 2).Width = 20;
            }

            int rowBeginIndex = 3;
            while (reader.Read())
            {
                sheet.Cell(2, 2).Value = "序号"; // 设置第二行第一列的标题为 "序号"
                for (int col = 0; col < reader.FieldCount; col++)
                {
                    string columnName = reader.GetName(col);
                    string customColumnName = columnMappings.ContainsKey(columnName) ? columnMappings[columnName] : columnName;
                    if (columnName == "duration")
                    {
                        sheet.Cell(2, 3 + col).Value = customColumnName + "(小时)";
                    }
                    else
                    {
                        sheet.Cell(2, 3 + col).Value = customColumnName;
                    }

                    if (string.Equals("duration", columnName))
                    {
                        try
                        {
                            var valueAry = reader[col].ToString().Split(':');
                            //换算成小时计算  
                            sheet.Cell(rowBeginIndex, col + 3).Value = Convert.ToString(Convert.ToInt32(valueAry[0]) * 24 + Convert.ToInt32(valueAry[1]) + Math.Round(Convert.ToDouble(valueAry[2]) / 60, 1));
                            // worksheet.Cell(row, col + 3).Value = valueAry[0] + "天" + valueAry[1] + "小时" + valueAry[2] + "分钟";
                        }
                        catch (Exception)
                        {

                            sheet.Cell(rowBeginIndex, col + 3).Value = reader[col].ToString();
                        }
                    }
                    else
                        sheet.Cell(rowBeginIndex, col + 3).Value = reader[col].ToString();
                }


                sheet.Cell(rowBeginIndex, 2).Value = (rowBeginIndex - 2).ToString(); // 在第二列显示计数器的值
                rowBeginIndex++;
            }
            sqlHelper.ClosedCon();
        }


        public static string encrypt(string str)
        {
            string encryptStr = "helloworld";
            string ret = "";
            for (int i = 0; i < str.Length; i++)
            {
                if (i < encryptStr.Length)
                {
                    ret += (char)(str[i] ^ encryptStr[i]);
                }
                else
                {
                    ret += (char)(str[i] ^ encryptStr[i % encryptStr.Length]);
                }
            }
            return ret;
        }

        public static string GetStrMd5(string ConvertString)
        {
            var md5 = new MD5CryptoServiceProvider();//实例化
            byte[] strbt = Encoding.UTF8.GetBytes(ConvertString);
            //对转换后的字节进行MD5加密
            byte[] result = md5.ComputeHash(strbt);
            string StrResult = BitConverter.ToString(result);
            StrResult = StrResult.Replace("-", "");
            for (int i = 0; i < result.Length; i++)
            {
                StrResult = StrResult + result[i].ToString("X2");
            }
            return StrResult;
        }
        //public static DateTime ConvertStrToDatetime(string str)
        //{
        //    //字符串转时间
        //    DateTime dt = DateTime.Now;
        //    try
        //    {
        //        dt = Convert.ToDateTime(str);
        //    }
        //    catch (Exception ex)
        //    {
        //        dt = DateTime.Now;
        //    }
        //    return dt;
        //}

        //得到机械码
        public static string GetMachCodeString()
        {
            string code = string.Empty;
            code = GetCpuId() + "-" + GetDiskName() + "-" + GetMacAddress();
            MD5Encode md5 = new MD5Encode();
            return md5.GetMd5Str(code);
        }

        private static string CMDPath = Environment.GetFolderPath(Environment.SpecialFolder.System) + "\\cmd.exe";
        public static void RunCMDCommand(string Command, out string OutPut)
        {
            using (var pc = new System.Diagnostics.Process())
            {
                Command = Command.Trim().TrimEnd('&') + "&exit";
                pc.StartInfo.FileName = CMDPath;
                pc.StartInfo.CreateNoWindow = true;
                pc.StartInfo.RedirectStandardError = true;
                pc.StartInfo.RedirectStandardInput = true;
                pc.StartInfo.RedirectStandardOutput = true;
                pc.StartInfo.UseShellExecute = false;
                pc.Start();
                pc.StandardInput.WriteLine(Command);
                pc.StandardInput.AutoFlush = true;
                OutPut = pc.StandardOutput.ReadToEnd();
                int P = OutPut.IndexOf(Command) + Command.Length;
                OutPut = OutPut.Substring(P, OutPut.Length - P - 3);
                pc.WaitForExit();
                pc.Close();
            }
        }

        /// <summary>
        /// 获取cpu序列号
        /// </summary>
        /// <returns></returns>
        public static string GetCpuId()
        {
            RunCMDCommand("wmic CPU get ProcessorID", out string processId);
            //市面上的电脑一般只安装一块CPU，我们直接取值即可
            processId = processId.Replace("ProcessorId", "").Replace("\n", "").Trim();
            return processId;
        }

        public static string GetDiskId()
        {
            RunCMDCommand("wmic DISKDRIVE get SerialNumber", out string diskId);
            diskId = diskId.Replace("SerialNumber", "").Replace("\n", "").Trim();
            return diskId;
        }
        public static string GetDiskName()
        {
            RunCMDCommand("wmic DISKDRIVE get Model", out string diskName);
            diskName = diskName.Replace("Model", "").Replace("\n", "").Trim();
            return diskName;
        }

        //cmd 获取可用的硬件码
        public static string GetMacAddress()
        {
            RunCMDCommand("wmic nic get MACAddress", out string macAddress);
            macAddress = macAddress.Replace("MACAddress", "").Replace("\n", "").Trim();
            return macAddress;
        }


        public static bool Verify()
        {
            RegisterInfo info = new RegisterInfo();
            info.machineCode = ProjectTools.GetMachCodeString();
            info.pointsNum = ProjectTools.pointsNum();

            string json = Newtonsoft.Json.JsonConvert.SerializeObject(info);

            string code = GetStrMd5(json);

            if (code == ProjectTools.RegisterKey())
            {
                return true;
            }
            return false;
            //TODO:暂时置为true，方便调试，更改完后需重新改回来
            // return true;
        }



    }
}
